package com.helloworld.upload.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.cfw.exception.BizException;
import com.helloworld.upload.domain.CellInfo;

public class ExcelUtil {
	private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

	/**
	 * 엑셀시트로부터 셀을 반환, 이미 생성되지 않은 셀일 경우 NULL반환
	 * @param sheet
	 * @param rowIndex
	 * @param columnIndex
	 * @return
	 */
	public static Cell getCell(XSSFSheet sheet, int rowIndex, int columnIndex) {
		return sheet.getRow(rowIndex).getCell(columnIndex);
	}
	
	/**
	 * 엑셀시트로부터 로그를 위한 셀을 반환, 해당셀에 대한 스타일도 지정하고, 
	 * 셀이 존재하지 않을 경우 새로 생성하여 반환
	 * @param sheet
	 * @param rowIndex
	 * @param columnIndex
	 * @param style
	 * @return
	 */
	public static Cell getLogCell(XSSFSheet sheet, int rowIndex, int columnIndex, CellStyle style) {
		Row row = sheet.getRow(rowIndex);
		
		if (row == null) { row = sheet.createRow(rowIndex); }
		
		Cell cell = row.getCell(columnIndex);
		
		if (cell == null) {
			cell = row.createCell(columnIndex);
			if (style != null) { cell.setCellStyle(style); }
		}
		
		return cell;
	}
	
	/**
	 * 파일명으로부터 인풋스트림 생성하여 반환
	 * 주의: 스트림 사용 후 close 수행 필수
	 * @param fileName
	 * @return
	 * @throws IOException
	 */
	public static FileInputStream getFileInputStream(String fileName) throws IOException {
		FileInputStream stream = new FileInputStream(fileName);
		return stream;
	}
	
	/**
	 * 파일명으로부터 아웃풋스트림 생성하여 반환
	 * 주의: 스트림 사용 후 close 수행 필수
	 * @param fileName
	 * @return
	 * @throws IOException
	 */
	public static FileOutputStream getFileOutputStream(String fileName) throws IOException {
		FileOutputStream stream = new FileOutputStream(fileName);
		return stream;
	}
	
	/**
	 * 스트림으로부터 첫번째 엑셀시트를 반환, 엑셀시트가 존재하지 않으면 NULL 반환
	 * @param stream
	 * @return
	 * @throws IOException
	 */
	public static XSSFSheet getExcelSheet(FileInputStream stream) throws IOException {
		XSSFWorkbook book = new XSSFWorkbook(stream);
		
		XSSFSheet sheet = null;
		if (book.getNumberOfSheets() > 0) {
			sheet = book.getSheetAt(0);
		}
		
		return sheet;
	}
	
	/**
	 * 파일을 복사하고 복사된 파일의 인풋스트림 반환
	 * @param sourceName
	 * @param targetName
	 * @return
	 * @throws Exception
	 */
	public static FileInputStream copyGetInputFileStream(String sourceName, String targetName) throws Exception {
		File sourceFile = null;
		File targetFile = null;
		FileInputStream stream = null;
		
		try {
			sourceFile = new File(sourceName);
			
			if (!sourceFile.exists() || !sourceFile.isFile() || !sourceFile.canRead()) {
				throw new IOException(sourceName);
			}
			
			targetFile = new File(targetName);
			
			FileUtils.copyFile(sourceFile, targetFile);
			
			stream = new FileInputStream(targetFile);
		} catch (Exception e) {
			if (stream != null) { stream.close(); }
			throw e;
		}
		
		return stream;
	}

	public static void writeResult(Map<Integer, List<CellInfo>> rowInfos, XSSFSheet sheet) throws IOException {
		if (sheet == null || rowInfos == null) { throw new BizException("E00001"); }
		
		Row topRow = sheet.getRow(0);
		
		if (topRow == null) { throw new BizException("E00001"); }
		
		int lastColIndex = topRow.getLastCellNum(); 
		sheet.setColumnWidth(lastColIndex, 15000);
		
		CellStyle style = sheet.getWorkbook().createCellStyle();
		style.setWrapText(true);
		
		for (int rowIndex : rowInfos.keySet()) {
			Cell cell = getLogCell(sheet, rowIndex, lastColIndex, style);
			
			StringBuilder builder = new StringBuilder();
			for (CellInfo cellInfo : rowInfos.get(rowIndex)) {
				List<CellInfo.Error> errors = cellInfo.getError();
				
				if (errors != null && errors.size() > 0) {
					if (StringUtils.isNotEmpty(builder.toString())) { builder.append("\n"); }
					builder.append("열:").append(cellInfo.getColumnIndex()).append(", 오류:");
				
					for (int i = 0; i < errors.size(); i++) {
						if (i > 0) { builder.append(", "); }
						builder.append(errors.get(i).getMessage());
					}
				}
			}
			logger.debug("*** str:" + builder.toString());
			cell.setCellValue(builder.toString());
		}
	}
	
}
